create    procedure amsp_GetSiblingTree
    @NavMenuID integer
as
begin

  declare
    @counter integer

  create table #temp (NavMenuID integer)

  /*
  ** First insert self and siblings based on ParentNavMenuID value
  */
  insert into #temp
  select NavMenuID
  from Nav_Menu
  where IsNull(ParentNavMenuID, -1) = (select IsNull(ParentNavMenuID, -1)
                                         from Nav_Menu
                                        where NavMenuID = @NavMenuID)
  
  /*
  ** As long as there are new children, keep adding them to #temp.
  ** To make sure a bad nav_menu item doesn't lock the server, we also
  ** limit this to 50 iterations.
  */
  set @counter = 0
  while @@rowCount > 0 and @counter < 50 begin
    set @counter = @counter + 1
    insert into #temp
    select a.NavMenuID
      from Nav_Menu a, #temp b
     where a.ParentNavMenuID = b.NavMenuID
       and a.NavMenuID not in (select NavMenuID from #temp)
  end

  /*
  ** Return our results
  */
  select * from #temp

end

GO
GRANT EXECUTE ON  [dbo].[amsp_GetSiblingTree] TO [IMIS]
GO